package cn.edu.scnu.semart.echats.mapper;

import cn.edu.scnu.semart.vo.user.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface UserStatMapper {
    // 直接贴刚才的 SQL
    @Select("WITH daily AS (" +
            "  SELECT DATE(created_at) AS d, COUNT(*) cnt FROM user GROUP BY d" +
            "), cumulative AS (" +
            "  SELECT d, SUM(cnt) OVER (ORDER BY d) AS cumulative FROM daily" +
            ")" +
            "SELECT DATE_FORMAT(d, '%Y-%m-%d') AS date, cumulative " +
            "FROM cumulative ORDER BY d")
    List<UserTrendVO> dailyCumulative();

    /* 用户类型占比 —— */
    @Select("SELECT user_type AS name, COUNT(*) AS value " +
            "FROM user " +
            "GROUP BY user_type")
    List<UserTypeRatioVO> typeRatio();

//    封禁占比
    @Select({
            "SELECT s.name, IFNULL(t.cnt, 0) AS value",
            "FROM (SELECT '激活' AS name, 'ACTIVE' AS code",
            "      UNION ALL",
            "      SELECT '封禁', 'BANNED') s",
            "LEFT JOIN (",
            "   SELECT status, COUNT(*) cnt",
            "   FROM user",
            "   GROUP BY status",
            ") t ON t.status = s.code"
    })
    List<UserStatusRatioVO> statusRatio();

    /* 每日新增关注数趋势 */
    @Select("SELECT DATE(created_at) AS date, COUNT(*) AS count " +
            "FROM user_follow " +
            "GROUP BY date " +
            "ORDER BY date")
    List<FollowDailyVO> followDaily();

    /* 力导向图：节点 + 关系 */
    @Select("SELECT u.user_id AS id, u.username AS name " +
            "FROM user u " +
            "WHERE u.status = 'ACTIVE'")
    List<GraphVO.Node> graphNodes();

    @Select("SELECT f.follower_id AS source, f.followed_id AS target " +
            "FROM user_follow f " +
            "JOIN user uf ON uf.user_id = f.follower_id AND uf.status = 'ACTIVE' " +
            "JOIN user ut ON ut.user_id = f.followed_id AND ut.status = 'ACTIVE'")
    List<GraphVO.Link> graphLinks();
}